/*
      Procedure:
      Check to see if STATUS = 'A' for valid records.

      DIRECTIONS:  The site pulls the column data dynamically from the metadata.
      It requires the ID as "dbid" column in there; the other columns are at the
      discretion of the stored procedure programmer.

    This is the default stored procedure.  Please do not edit this and edit
    BAERosterManagementAvailableRecords and reflect the stored procedure in
    the admin configuration.
*/
CREATE PROCEDURE [dbo].[BAERosterManagementAvailableRecordsDefault]
      @UID VARCHAR(10),
      @sortColumn VARCHAR(300),
      @isAsc tinyint
AS
      DECLARE @COID VARCHAR(50);
      DECLARE @columnSortDirection VARCHAR(30);
      DECLARE @sql NVARCHAR(1000);

      SET @COID = ISNULL((SELECT ID FROM Name WHERE ID = @UID and COMPANY_RECORD = 1),0);
      IF (@COID = 0)
      BEGIN
              SET @COID = ISNULL((SELECT CO_ID FROM Name WHERE ID = @UID),0);
      END
      IF (@COID = 0)
      BEGIN
              SET @COID = (SELECT ID FROM Name WHERE ID = @UID);
      END

      IF (@isAsc = 1)
      BEGIN
            SET @columnSortDirection = 'ASC';
      END
      ELSE
      BEGIN
            SET @columnSortDirection = 'DESC';
      END

      SET @sql = 'SELECT ID as "dbid", FIRST_NAME as "First Name", LAST_NAME as "Last Name",  MTT.DESCRIPTION as "Member Type", CITY as "City", STATE_PROVINCE as "State" FROM Name as NT JOIN Member_Types as MTT ON NT.MEMBER_TYPE = MTT.MEMBER_TYPE WHERE CO_ID = ' + @COID + ' AND STATUS = ''A'' AND NT.COMPANY_RECORD != 1 ORDER BY ' + @sortColumn + ' ' + @columnSortDirection;

      EXEC sp_executesql @sql
GO